#!/usr/bin/env python
# -*- coding: utf-8 -*-

from threading import Timer

import datetime
import MysqlUtils
import Config
import os


def monitor():
    """
    监控函数
    :return:
    """
    # 当前检查执行时间
    now = datetime.datetime.now()
    print "监控执行 -- %s" % now.strftime('%Y-%m-%d %H:%M:%S')

    db = MysqlUtils.MysqlHelper()
    # 检查最近3分钟内是否有新的数据产生 --- 开始
    result = db.query(
        'select count(add_time) as data_count from natgas_flowmeterdata where add_time >= now()-interval 3 MINUTE'
    )
    if result and int(result[0]['data_count']) == 0:
        # 最近3分钟没有新数据产生，重启下服务
        os.system('supervisorctl restart UdpServer && (echo "====`date`====" >> /home/workspace/natgas_server/log/UdpServerMonitor.log)')
    # 检查最近3分钟内是否有新的数据产生 --- 结束
    # 归档本月之前的历史的数据 --- 开始
    # 每天凌晨1点到早上6点之间才执行归档数据的操作 & 删除natgas_flowmeterdata_today 2天之外的数据
    if 0 < now.hour < 6:
        #  删除natgas_flowmeterdata_today 2天之外的数据
        today_sql = "DELETE FROM natgas_flowmeterdatatoday where DATE_SUB(CURDATE(), INTERVAL 2 DAY) > date(add_time)"
        db.dml(today_sql)

        # 获取到要归档的月份
        result = db.query('select distinct(date_format(add_time,\'%Y%m\')) as archive_month from natgas_flowmeterdata')
        # 根据已有数据生成表
        for r in result:
            # 如果是当月的归档，先跳过
            if now.strftime('%Y%m') == r['archive_month']:
                continue
            # 根据已有归档数据生成表的记录
            insert_sql = """
                INSERT INTO natgas_flowmeterdatamonthrecord(yearmonth)
                SELECT '%s' FROM DUAL WHERE not exists (select * from natgas_flowmeterdatamonthrecord
                WHERE yearmonth = '%s')
                """ % (r['archive_month'], r['archive_month'])
            db.dml(insert_sql)
            # 根据归档生成存档的表名
            archive_table = 'natgas_flowmeterdata_%s' % r['archive_month']
            # 如果不存在该表就创建这个表
            create_sql = """
            CREATE TABLE IF NOT EXISTS `%s` (
              `id` int(11) NOT NULL,
              `flowmeter_type` varchar(2) NOT NULL,
              `flowmeter_total_g` varchar(30) NOT NULL,
              `flowmeter_total_b` varchar(30) NOT NULL,
              `flowmeter_g` varchar(20) NOT NULL,
              `flowmeter_b` varchar(20) NOT NULL,
              `flowmeter_temperature` varchar(20) NOT NULL,
              `flowmeter_pressure` varchar(20) NOT NULL,
              `flowmeter_status` varchar(10) NOT NULL,
              `add_time` datetime(6) NOT NULL,
              `flowmeter_id` int(11) NOT NULL,
              PRIMARY KEY (`id`),
              INDEX(`add_time`),
              FOREIGN KEY (`flowmeter_id`) REFERENCES `natgas_flowmeterreg` (`id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
            """ % archive_table
            db.dml(create_sql)

        # 将不是当月的数据更新到对应的存档表格里面
        for r in result:
            # 如果是当月的归档，先跳过
            if now.strftime('%Y%m') == r['archive_month']:
                continue
            # 根据归档生成存档的表名
            archive_table = 'natgas_flowmeterdata_%s' % r['archive_month']
            # 查询历史数据归档到对应的表格里面
            try:
                # 查看是否要归档的数据,如果要归档的数据数量为0则跳过
                archive_result = db.query('select count(*) as archive_count from natgas_flowmeterdata where date_format(add_time,\'%%Y%%m\')=\'%s\'' % r['archive_month'])
                if archive_result and int(archive_result[0]['archive_count']) == 0:
                    continue
                # 要执行的sql清单
                sql_list = [
                    'insert into %s select * from natgas_flowmeterdata where date_format(add_time,\'%%Y%%m\')=\'%s\'' % (archive_table, r['archive_month']),
                    'delete from natgas_flowmeterdata where date_format(add_time,\'%%Y%%m\')=\'%s\'' % r['archive_month']
                ]
                # 执行归档数据(事务)
                db.dml_tran(sql_list)
            except Exception as e:
                print '批量插入存档数据发生异常:', e
        # 归档本月之前的历史的数据 --- 结束
    # 每天早上8点到8点10分统计归档上一日的工况和标况的日累计量
    if now.hour == 8 and now.minute <= 20:
        query_time = now.strftime('%Y-%m-%d')
        query_time_yestoday = (now + datetime.timedelta(days=-1)).strftime('%Y-%m-%d')
        # 检查上一日是否已经有该流量计的统计信息，没有的话才统计并加入到表格。
        sql = """
        select fr.flowmeter_name, t2.flowmeter_type, CONCAT(t2.flowmeter_temperature,'(',t1.flowmeter_temperature,')'), CONCAT(t2.flowmeter_pressure,'(',t1.flowmeter_pressure,')'), CONCAT(t2.flowmeter_g,'(',t1.flowmeter_g,')'),
                CONCAT(t2.flowmeter_b,'(',t1.flowmeter_b,')'), CONCAT(t2.flowmeter_total_g,'(',t1.flowmeter_total_g,')'), CONCAT(t2.flowmeter_total_b,'(',t1.flowmeter_total_b,')'),
                (t2.flowmeter_total_g-t1.flowmeter_total_g) as g_ryq, (t2.flowmeter_total_b-t1.flowmeter_total_b) as b_ryq,
                 CONCAT(date_format(t2.add_time,'%%Y-%%m-%%d %%H:%%i:%%s'),'(',date_format(t1.add_time,'%%Y-%%m-%%d %%H:%%i:%%s'),')'), t2.flowmeter_status, cr.name as cr_name, s.name as sname, c.name as cname, cr.cno, fr.flowmeter_id, fr.id, fr.flowmeter_spec from
    (select * from (select * from natgas_flowmeterdata WHERE DATE_FORMAT(add_time,'%%Y-%%m-%%d')=date_sub('%s', interval 1 day) AND HOUR(add_time)<=7 order by id desc) as a group by flowmeter_id) as t1,
    (select * from (select * from natgas_flowmeterdata WHERE DATE_FORMAT(add_time,'%%Y-%%m-%%d')='%s' AND HOUR(add_time)<=7 order by id desc) as a group by flowmeter_id) as t2,
    natgas_flowmeterreg fr,natgas_concentratorreg cr,natgas_site s,natgas_company c
    where t1.flowmeter_id=t2.flowmeter_id and t2.flowmeter_id=fr.id and fr.cno_id=cr.id
    and cr.site_id=s.id and s.company_id=c.id
        """ % (query_time, query_time)
        result = db.query(sql)
        # 根据已有数据插入统计数据
        for r in result:
            # 查询是否已经有上一日的统计数据,没有的话则插入
            insert_sql = """
            INSERT INTO natgas_flowmeterdatadaytotal(flowmeter_g, flowmeter_b, add_time, flowmeter_id)
            SELECT '%s', '%s', '%s', '%s' FROM DUAL WHERE not exists (select * from natgas_flowmeterdatadaytotal
            WHERE flowmeter_id = '%s' AND DATE_FORMAT(add_time,'%%Y-%%m-%%d') = '%s')
            """ % (r['g_ryq'], r['b_ryq'], query_time_yestoday, r['id'], r['id'], query_time_yestoday)
            db.dml(insert_sql)

        # 每天8点之后统计最临近8点的数据
        sql_hour8="""
        insert into natgas_flowmeterdatahour8
        select * from (select * from natgas_flowmeterdatatoday order by add_time desc) natgas_flowmeterdatatoday
        where date_format(add_time,'%%Y-%%m-%%d')='%s' and hour(add_time)<=7 group by flowmeter_id order by add_time desc
        """ % query_time
        db.dml(sql_hour8)

    # 关闭数据库连接
    db.close()
    # 定时执行
    Timer(Config.UDP_TIMER_INTERVAL, monitor).start()


if __name__ == '__main__':
    monitor()

